﻿#region Using directives

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using Microsoft.ApplicationBlocks.Data;
using MCMLXVII.BackEnd.Data.OrganizationChart.DataSets;
using MCMLXVII.Core.Common;
using System.Data;
using MCMLXVII.BackEnd.Services.Common.Messages;
using System.ServiceModel;

#endregion

namespace MCMLXVII.BackEnd.Data.OrganizationChart
{
    public class TreeMembers
    {
        public TreeMembers()
        {

        }

        #region Get and List Functions
        public static void AdvancedGetHeadNodeID(int MemberID, out int HeadNodeID, out string HeadNodeName)
        {
            DbDataReader dr =  SqlHelper.ExecuteReader("dbo.GetMemberHeadNode", new object[] { MemberID });
            if (dr.Read())
            {
                HeadNodeID = dr.GetInt32(0);
                HeadNodeName = dr.GetString(1);
            }
            else
            {
                HeadNodeID = 0;
                HeadNodeName = "Not Found";
            }
            dr.Close();
        }


        public static msgMembershipInfo[] AdvancedFindMembersUnderHeadNode(int HeadNodeID, FilterItem[] Filter, bool IncludeFinalUsers, bool IncludeGroups, bool IncludeNodes, bool IncludeRoles, bool IncludeSecuritySelectables)
        {
            string QueryPart1 = "Select O.MemberID, O.Description, O.MemberClass from OrganizationMembers O inner join OrganizationMembersClasses C on O.MemberClass=C.OrganizationMemberClassID ";
            
            string QueryPart2 = " where ";
            bool AndReq = false;
            foreach(FilterItem f in Filter)
            {
                switch (f.PropertyName.ToLower())
                { 
                    case "memberid":
                    case "description":
                    case "nodeid":
                    case "memberclass":
                    case "securityproviderid":
                    case "logonname":
                    case "directoryidentifier":
                    case "language":
                    case "rolememberid":
                        if (AndReq)
                            QueryPart2  += " and ";
                        else
                            AndReq = true;
                        QueryPart2  += " O." + f.PropertyName + "='" + f.Value + "'";
                        break;
                    case "groupdescription":
                        QueryPart1 += " inner join OrganizationGroupsMembers OG on O.MemberId = OG.MemberMemberID ";
                        if (AndReq)
                            QueryPart2 += " and ";
                        else
                            AndReq = true;
                        QueryPart2 += " OG.MemberID in ( dbo.GetOrganizationGroupsByNodeID( " + HeadNodeID + ",'" + f.Value +"' )) ";
                        break;
                    default:
                        if (AndReq)
                            QueryPart2  += " and ";
                        else
                            AndReq = true;
                        QueryPart2  += " O.ClassProperties.value('(/Properties/" + f.PropertyName + ")[1]', 'varchar(50)') = '" + f.Value + "'";
                        break;
                }
            }
            if (IncludeFinalUsers)
            {
                if (AndReq)
                    QueryPart2  += " and ";
                else
                    AndReq = true;
                QueryPart2  += " C.IdentifiesFinalUser='Y' ";
            }
            if (IncludeGroups)
            {
                if (AndReq)
                    QueryPart2  += " and ";
                else
                    AndReq = true;
                QueryPart2 += " C.IsGroup='Y' ";
            }
            if (IncludeNodes)
            {
                if (AndReq)
                    QueryPart2 += " and ";
                else
                    AndReq = true;
                QueryPart2 += " C.IsNode='Y' ";
            }
            if (IncludeRoles )
            {
                if (AndReq)
                    QueryPart2 += " and ";
                else
                    AndReq = true;
                QueryPart2 += " C.IsRole='Y' ";
            }
            if (IncludeSecuritySelectables)
            {
                if (AndReq)
                    QueryPart2 += " and ";
                else
                    AndReq = true;
                QueryPart2 += " C.IsSecuritySelectable='Y' ";
            }
            if (AndReq)
                QueryPart2 += " and ";
            else
                AndReq = true;

            QueryPart1 += QueryPart2 + " O.NodeID in (select NodeID from dbo.GetNodebranch(" + HeadNodeID.ToString() + "))";

            DSMembersLists.FindOrganizationMembersByDescriptionDataTable dt = new DSMembersLists.FindOrganizationMembersByDescriptionDataTable();
            dt.Load( SqlHelper.ExecuteReader(CommandType.Text, QueryPart1 ));
            if (dt.Rows.Count == 0)
                return null;
            else
            {
                int i = 0;
                msgMembershipInfo[] res = new msgMembershipInfo[dt.Rows.Count];
                foreach(DataRow dr in dt.Rows)
                {
                    DSMembersLists.FindOrganizationMembersByDescriptionRow row = (DSMembersLists.FindOrganizationMembersByDescriptionRow)dr;
                    res[i] = new msgMembershipInfo(row.MemberID, row.MemberClass, row.Description);
                    i++;
                }
                return res;
            }
    
        }



        public static DSMembersLists.FindOrganizationMembersByDescriptionDataTable FindOrganizationMembersByDescription(string Filter)
        {
            DSMembersLists.FindOrganizationMembersByDescriptionDataTable dt = new DSMembersLists.FindOrganizationMembersByDescriptionDataTable();
            dt.Load(SqlHelper.ExecuteReader("dbo.FindOrganizationMembersByDescription", new object[] { Filter }));
            if (dt.Rows.Count > 0) 
                return dt;
            else
                return null;
        }

        public static DSOrganizationTreeMemberInfo GetOrganizationMemberInfo(int MemberID, int RequestingMemberID, bool IncludeGroups, bool IncludeRoles, bool IncludeHeadNode)
        {
            DSOrganizationTreeMemberInfo ds = new DSOrganizationTreeMemberInfo();
            ds.Tables["OrganizationMemberInfo"].Load(SqlHelper.ExecuteReader("dbo.GetOrganizationMemberInfoByID", new object[] { MemberID, RequestingMemberID }));
            if (ds.Tables["OrganizationMemberInfo"].Rows.Count > 0)
            {
                //Load Group MemberShip Info                
                if (IncludeGroups)
                    ds.Tables["MemberOf"].Load(SqlHelper.ExecuteReader("dbo.GetOrganizationMembersOf", new object[] { MemberID }));
                if (IncludeRoles)
                {
                    ds.Tables["MemberOfRole"].Load(SqlHelper.ExecuteReader("dbo.GetOrganizationRoleMember", new object[] { MemberID }));
                    if (ds.Tables["MemberOfRole"].Rows.Count == 0)
                        ds.Tables["MemberOfRole"].Rows.Add(0, "", 0);
                }
                if (IncludeHeadNode)
                {
                    ds.Tables["HeadNode"].Load(SqlHelper.ExecuteReader("dbo.GetMemberHeadNode", new object[] { MemberID }));
                }
                return ds;
            }
            else
                return null;
        }


        public static DSMembersClassMembers.OrganizationMembersClassesMembersDataTable GetOrganizationMembersClassesMembers(int MemberID)
        {
            DSMembersClassMembers.OrganizationMembersClassesMembersDataTable dt = new DSMembersClassMembers.OrganizationMembersClassesMembersDataTable();
            dt.Load(SqlHelper.ExecuteReader("dbo.GetOrganizationMembersClassMembers", new object[] { MemberID }));
            if (dt.Rows.Count > 0)
                return dt;
            else
                return null;

        }

        public static DSOrganizationNodeMembers.GetOrganizationTreeNodeMembersDataTable GetOrganizationTreeNodeMembers(int NodeID, int MemberID)
        {
            DSOrganizationNodeMembers.GetOrganizationTreeNodeMembersDataTable dt = new DSOrganizationNodeMembers.GetOrganizationTreeNodeMembersDataTable();
            dt.Load(SqlHelper.ExecuteReader("GetOrganizationTreeNodeMembers", new object[] { NodeID }));
            if (dt.Rows.Count > 0)
                return dt;
            else
                return null;

        }

        public static DSOrganizationAudit.MemberAuditDataTable GetOrganizationMemberAudit(int MemberID)
        {
            DSOrganizationAudit.MemberAuditDataTable dt = new DSOrganizationAudit.MemberAuditDataTable ();
            dt.Load(SqlHelper.ExecuteReader("dbo.GetOrganizationMemberAudit", new object[] { MemberID }));
            if (dt.Rows.Count > 0)
                return dt;
            else
                return null;
        }


        #endregion

        #region Create, Update and Delete Functions

        public static DSOrganizationTreeNewItem.NewCreatedItemRow CreateNewOrganizationMember(int NodeID, string Name, int MemberClass, string ClassProperties, int SecurityProviderID, string LogonName, string DirectoryIdentifier, string Language, int RoleMemberID, int CreatorMemberID)
        {
            DSOrganizationTreeNewItem.NewCreatedItemDataTable dt = new DSOrganizationTreeNewItem.NewCreatedItemDataTable();
            dt.Load(SqlHelper.ExecuteReader("CreateNewOrganizationMember", NodeID, Name, MemberClass, ClassProperties, SecurityProviderID, LogonName, DirectoryIdentifier, Language, RoleMemberID, CreatorMemberID));
            if (dt.Rows.Count > 0)
                return (DSOrganizationTreeNewItem.NewCreatedItemRow)dt.Rows[0];
            else
                return null;
        }

        public static int UpdateOrganizationMember(int MemberID, string Name, string ClassProperties, int SecurityProviderID, string LogonName, string DirectoryIdentifier, string Language, int RoleMemberID, int UpdateSequence, int UpdateMemberID)
        {
            return (int)SqlHelper.ExecuteScalar("UpdateOrganizationMember", MemberID, Name, ClassProperties, SecurityProviderID, LogonName, DirectoryIdentifier, Language, RoleMemberID, UpdateSequence, UpdateMemberID);
        }

        public static int UpdateOrganizationGroupMembership(int GroupMemberID, int[] Members, int UpdateSequence, int UpdateMemberID)
        {
            //DbTransaction tran = SqlHelper.CreateTransaction();
            int NewUpdateSequence = 0;
            try
            {

                NewUpdateSequence = (int)SqlHelper.ExecuteScalar("dbo.UpdateOrganizationGroupMembership_A", GroupMemberID, UpdateSequence, UpdateMemberID );
                foreach (int MemberMemberID in Members)
                {

                    SqlHelper.ExecuteNonQuery("dbo.UpdateOrganizationGroupMembership_B", GroupMemberID, MemberMemberID );
                }
                //tran.Commit();
            }
            catch (DbException ex)
            {
                //tran.Rollback();
                if (ex.ErrorCode == 500001)
                {
                    throw new FaultException<BackendFault>(new BackendFault(BackendFaulType.DataUpdateSequence ));
                }
                else
                {
                    throw new FaultException<BackendFault>(new BackendFault(BackendFaulType.Technical, ex.Message ));
                }
            }
            return NewUpdateSequence;
        }


        public static int MoveOrganizationMember(int MemberID, int NodeID, int UpdateSequence, int UpdateMemberID)
        {
            return (int)SqlHelper.ExecuteScalar("MoveOrganizationMember", MemberID, NodeID, UpdateSequence, UpdateMemberID);
        }

        public static bool DeleteOrganizationMember(int MemberID, int DeleteMemberID)
        {
            try
            {
                SqlHelper.ExecuteNonQuery("DeleteOrganizationMember", MemberID, DeleteMemberID);
                return true;
            }
            catch
            {
                return false;
            }

        }
        #endregion
        
    }
}
